/*
	EXEC SP_SAVE_INFORMATIVE_BINNING_INFO 87522,6,'Bin1',275, 0, 0
*/

ALTER PROC SP_SAVE_INFORMATIVE_BINNING_INFO 
(
	@LOT_ID		INT,
	@WAFER_ID	INT,
	@BIN_NUMBER VARCHAR(50),
	@BIN_QTY	INT,
	@REJECTED_QTY	INT,
	@YIELD		FLOAT		
)
 	
 AS
 BEGIN
 	
	DECLARE @PROCESSTEP_ID INT
	DECLARE @GORSS_DIE_PER_WAFER INT
	DECLARE @PS_TYPE VARCHAR(50)
	DECLARE @RELEASE_COUNT INT

	SELECT @PROCESSTEP_ID = LT.PROCESSSTEP_ID, @PS_TYPE = PS_TYPE FROM LOT_TABLE LT, PROCESS_STEP PS WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LOT_ID = @LOT_ID
	
	SELECT @GORSS_DIE_PER_WAFER = ISNULL(Parameter_VALUE,0) FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT TOP 1 PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSTEP_ID,0) WHERE PS_TYPE = 'FAB' ) AND Parameter_Key = 'Gross Die/Wafer'		
	
	IF @YIELD IS NULL OR @YIELD = 0 OR @YIELD = 0.0
	BEGIN
			IF @GORSS_DIE_PER_WAFER > 0
			BEGIN
				SET @YIELD = ROUND( ( @BIN_QTY * 100.00 ) / @GORSS_DIE_PER_WAFER , 2)

				SET @REJECTED_QTY = @GORSS_DIE_PER_WAFER - @BIN_QTY		
			END
	END			
	
	DECLARE @DB_LOT_ID INT
	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @SORT_COLUMNSTRING NVARCHAR(100)
	DECLARE @INFO_COLUMNSTRING NVARCHAR(100)
	DECLARE @PARAMDEFINITION NVARCHAR(100)
	DECLARE @SORT_QUERYSTRING NVARCHAR(200)
	DECLARE @DIEBANK_QUERYSTRING NVARCHAR(200)
	DECLARE @INFORMATIVE_QUERYSTRING NVARCHAR(200)
	DECLARE @SELECT_STRING NVARCHAR(200)
	DECLARE @BIN_COLUMN_NO VARCHAR(50)
	DECLARE @BIN_BALENCE_NO VARCHAR(50)		
	DECLARE @count 		INT
	DECLARE @DIE_BANK_BIN_MODIFIED BIT

	SET @BIN_COLUMN_NO = REPLACE(@BIN_NUMBER, 'BIN','BIN_')	
	SET @BIN_BALENCE_NO = @BIN_COLUMN_NO + '_BALANCE_QTY'
	
	IF ( SELECT COUNT(*) FROM INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID )  = 0
	BEGIN
		
		SET @SORT_QUERYSTRING = N'INSERT INTO SORT_BIN_RELEASE_TABLE (LOT_ID, WAFER_ID ,'+@BIN_COLUMN_NO + ', '+@BIN_BALENCE_NO + ') 
			VALUES('+CONVERT(NVARCHAR, @LOT_ID)+' ,'+CONVERT(NVARCHAR,@WAFER_ID)+','+CONVERT(NVARCHAR,@BIN_QTY)+','+CONVERT(NVARCHAR,@BIN_QTY)+');'
		SET @INFORMATIVE_QUERYSTRING = N'INSERT INTO INFORMATIVE_BINNING (LOT_ID, WAFER_ID ,GOOD_DICE,YIELD,'+@BIN_COLUMN_NO + ','+'BIN_REJECTED)
			VALUES('+CONVERT(NVARCHAR,@LOT_ID)+' ,'+CONVERT(NVARCHAR,@WAFER_ID)+','+CONVERT(NVARCHAR,@BIN_QTY)+','+CONVERT(NVARCHAR,@YIELD)+','+CONVERT(NVARCHAR,@BIN_QTY)+','+CONVERT(NVARCHAR,@REJECTED_QTY)+');'
					
		EXECUTE SP_EXECUTESQL @INFORMATIVE_QUERYSTRING  -- INSERTS INTO INFORMATIVE BINNING
		EXECUTE SP_EXECUTESQL @SORT_QUERYSTRING				
		
		SET @SORT_QUERYSTRING = null
		SET @INFORMATIVE_QUERYSTRING = null
		SET @BIN_BALENCE_NO = null
		SET @BIN_COLUMN_NO  = null
	END
	ELSE
	BEGIN		
					 			
		SET @SORT_COLUMNSTRING = CONVERT(NVARCHAR,@BIN_COLUMN_NO) + ' = ' + CONVERT(NVARCHAR,@BIN_QTY) + ' , '+ CONVERT(NVARCHAR,@BIN_BALENCE_NO) +' = ' + CONVERT(NVARCHAR,@BIN_QTY)
		SET @INFO_COLUMNSTRING = CONVERT(NVARCHAR,@BIN_COLUMN_NO) + ' = ' + CONVERT(NVARCHAR,@BIN_QTY) + ' , YIELD = ' + CONVERT(NVARCHAR,@YIELD) + ' , GOOD_DICE = ' + CONVERT(NVARCHAR,@BIN_QTY) + ' , BIN_REJECTED = ' + CONVERT(NVARCHAR,@REJECTED_QTY)
		SET @SORT_QUERYSTRING = N'UPDATE SORT_BIN_RELEASE_TABLE SET '+@SORT_COLUMNSTRING+' WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID' 
		SET @INFORMATIVE_QUERYSTRING = N'UPDATE INFORMATIVE_BINNING SET '+@INFO_COLUMNSTRING+' WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID' 
		SET @PARAMDEFINITION = N'@LOT_ID INT,@WAFER_ID INT'
		
		-- To Update the informative binning// if modified through die bank binning info no need to Update
		SELECT @DIE_BANK_BIN_MODIFIED = DIE_BANK_BIN_INFO_UPDATE FROM INFORMATIVE_BINNING WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
		IF @DIE_BANK_BIN_MODIFIED = 1 
		BEGIN
			IF @PS_TYPE = 'Die Bank'
			BEGIN
				SELECT @RELEASE_COUNT = COUNT(*) FROM ASSM_RELEASE_LOT_TABLE ARL,ASSM_RELEASE_TABLE AR WHERE ARL.ASSEMBLY_RELEASE_ID = AR.ASSEMBLY_RELEASE_ID 
						and STATUS <> 'Cancelled'  AND LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
				IF @RELEASE_COUNT = 0
				BEGIN
					EXECUTE SP_EXECUTESQL @INFORMATIVE_QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID, @WAFER_ID = @WAFER_ID
					EXECUTE SP_EXECUTESQL @SORT_QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID, @WAFER_ID = @WAFER_ID	
				END
			END
			ELSE
			BEGIN
				EXECUTE SP_EXECUTESQL @INFORMATIVE_QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID, @WAFER_ID = @WAFER_ID
				EXECUTE SP_EXECUTESQL @SORT_QUERYSTRING, @PARAMDEFINITION, @LOT_ID = @LOT_ID, @WAFER_ID = @WAFER_ID	
			END
		END
				
		
		SET @SORT_QUERYSTRING = null
		SET @INFORMATIVE_QUERYSTRING = null
		SET @BIN_BALENCE_NO = null
		SET @BIN_COLUMN_NO  = null
	END

	--FOR INSERTING BININFO for Already Exist Diebank Lot, when the partial Binning Received for Other wafers
	SELECT @LOT_NUMBER = LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
	SELECT @DB_LOT_ID  = LOT_ID FROM LOT_TABLE LT, PROCESS_STEP PS WHERE PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND PS_TYPE = 'DIE BANK' AND LOT_NUMBER = @LOT_NUMBER
	IF @DB_LOT_ID IS NOT NULL
	BEGIN
		IF ( SELECT COUNT(*) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @DB_LOT_ID AND WAFER_ID = @WAFER_ID )  = 0
		BEGIN
			 SET @DIEBANK_QUERYSTRING = N'INSERT INTO SORT_BIN_RELEASE_TABLE (LOT_ID, WAFER_ID ,'+@BIN_COLUMN_NO + ', '+@BIN_BALENCE_NO + ') 
				VALUES('+CONVERT(NVARCHAR, @DB_LOT_ID)+' ,'+CONVERT(NVARCHAR,@WAFER_ID)+','+CONVERT(NVARCHAR,@BIN_QTY)+','+CONVERT(NVARCHAR,@BIN_QTY)+');'

			 EXECUTE SP_EXECUTESQL @DIEBANK_QUERYSTRING
			
			SET @DIEBANK_QUERYSTRING = null
		END
	END
			
END
